package com.itheima.mapper;

import com.itheima.pojo.vo.ArticleDTO;
import com.itheima.pojo.vo.CategoryArticleCountDTO;
import com.itheima.pojo.vo.CommentDTO;
import com.itheima.pojo.vo.MonthlyArticleCountDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @Author: Insight
 * @Description: TODO
 * @Date: 2024-06-11 20:18
 * @Version: 1.0
 */
@Mapper
public interface DashBoardMapper {

    @Select("select count(*) from article")
    Integer countNewsTotal();

    @Select("SELECT COUNT(*) FROM user WHERE role = 'USER'")
    Integer countUsersTotal();

    @Select("SELECT COUNT(*) AS total_comments\n" +
            "FROM (\n" +
            "    SELECT id FROM comment\n" +
            "    UNION ALL\n" +
            "    SELECT id FROM subcomment\n" +
            ") AS combined_comments")
    Integer countCommentsTotal();
    @Select("SELECT \n" +
            "    COUNT(*) AS total_comments\n" +
            "FROM (\n" +
            "    SELECT \n" +
            "        DATE(create_time) AS comment_date \n" +
            "    FROM \n" +
            "        comment \n" +
            "    WHERE \n" +
            "        DATE(create_time) = CURDATE()\n" +
            "    UNION ALL\n" +
            "    SELECT \n" +
            "        DATE(create_time) AS comment_date \n" +
            "    FROM \n" +
            "        subcomment \n" +
            "    WHERE \n" +
            "        DATE(create_time) = CURDATE()\n" +
            ") AS combined_dates")
    Integer countDaliyCommentsTotal();

    @Select("SELECT DATE_FORMAT(create_time, '%Y-%m') AS month, COUNT(*) AS article_count " +
            "FROM article " +
            "GROUP BY DATE_FORMAT(create_time, '%Y-%m') " +
            "ORDER BY month")
    List<MonthlyArticleCountDTO> getMonthlyArticleCounts();

    @Select("SELECT c.category_name AS categoryName, COUNT(a.id) AS articleCount " +
            "FROM category c " +
            "LEFT JOIN article a ON c.id = a.category_id " +
            "GROUP BY c.id")
    List<CategoryArticleCountDTO> getrateArticles();

    @Select("SELECT a.id, a.title, a.content,a.cover_img,a.create_time, u.username AS create_user FROM article a " +
            "JOIN user u ON a.create_user = u.id ORDER BY a.create_time DESC LIMIT 5")
    List<ArticleDTO> getLatestArticles();

    @Select("SELECT c.id,c.content,c.create_time,u.nickname as nickname FROM comment c LEFT JOIN user u "+
    "on c.create_user = u.id ORDER BY c.create_time DESC LIMIT 5")
    List<CommentDTO> getLatestComments();
}
